const db = require('../dataBase/db')
const _ = require('lodash');

const post = {
    async create(postData) {
        // 校验 content 是否为合法 JSON
        try {
            JSON.parse(JSON.stringify(postData.content));
        } catch (err) {
            throw new Error('content 必须是有效的 JSON 格式');
        }

        const viewCount = 0;
        const likeCount = 0;
        const contentJson = JSON.stringify(postData.content);

        const [result] = await db.query(
            `INSERT INTO posts SET 
            title = ?, 
            content = ?,  -- 这里直接插入字符串
            author_id = ?,
            status = ?,
            view_count = ?,
            like_count = ?`,
            [
                postData.title,
                contentJson,  // 转换后的 JSON 字符串
                postData.author_id,
                postData.status || 'published',
                viewCount,
                likeCount
            ]
        );

        return this.getById(result.insertId);
    },
    async getAll() {
        const [rows] = await db.query(
            `SELECT * FROM posts`
        );
        return rows;
    },
    async getById(postId) {
        try {
            // 执行 JOIN 查询
            const [rows] = await db.query(`
              SELECT 
                posts.*,
                users.username 
              FROM posts
              LEFT JOIN users 
                ON posts.author_id = users.user_id
              WHERE posts.post_id = ?
            `, [postId]);

            // 处理空结果
            if (!rows.length) return null;

            // 格式化返回数据
            return {
                ...rows[0],
                username: rows[0].username || null // 用户不存在时返回 null
            };

        } catch (error) {
            console.error(`JOIN 查询失败: ${error.message}`);
            throw new Error('获取帖子详情失败');
        }
    },

    async getPaginated(cursor = null, pageSize = 10, authorId = null) {
        // 构建 WHERE 条件
        const conditions = []
        const params = []

        if (authorId) {
            conditions.push('posts.author_id = ?')
            params.push(authorId)
        }

        if (cursor) {
            conditions.push('posts.post_id < ?')
            params.push(cursor)
        }

        // 组合 WHERE 子句
        let whereClause = ''
        if (conditions.length > 0) {
            whereClause = `WHERE ${conditions.join(' AND ')}`
        }

        // 构建完整查询
        const query = `
        SELECT 
            posts.*,
            users.username 
        FROM posts
        LEFT JOIN users 
            ON posts.author_id = users.user_id
        ${whereClause}
        ORDER BY posts.post_id DESC
        LIMIT ?
    `

        params.push(pageSize + 1) // 多取1条判断是否还有数据

        const [rows] = await db.query(query, params)
        const hasMore = rows.length > pageSize
        const items = hasMore ? rows.slice(0, -1) : rows

        return {
            items,
            next_cursor: items.length ? items[items.length - 1].post_id : null,
            has_more: hasMore
        }
    },


    async update(postId, updateData) {
        // 显式指定允许更新的字段
        const allowedFields = ['title', 'content', 'updated_at']
        const filteredData = _.pick(updateData, allowedFields)

        // 添加类型校验（防止SQL注入）
        if (filteredData.title && typeof filteredData.title !== 'string') {
            throw new Error('Invalid title type')
        }

        await db.query(
            `UPDATE posts SET ? WHERE post_id = ?`,
            [filteredData, postId]
        )
        return this.getById(postId)
    },

    async delete(postId) {
        // 先检查是否存在（避免无意义的删除操作）
        const exist = await this.getById(postId)
        if (!exist) throw new Error('Post not exist')

        // 使用事务处理关联数据删除（示例）
        const connection = await db.getConnection()
        try {
            await connection.beginTransaction()

            // // 删除关联评论（假设有comments表）
            // await connection.query(
            //     `DELETE FROM comments WHERE post_id = ?`,
            //     [postId]
            // )

            // 删除主记录
            await connection.query(
                `DELETE FROM posts WHERE post_id = ?`,
                [postId]
            )

            await connection.commit()
            return true
        } catch (err) {
            await connection.rollback()
            throw err
        } finally {
            connection.release()
        }
    },

    async incrementViews(postId) {
        await db.query(
            `UPDATE posts SET view_count = view_count + 1 
         WHERE post_id = ?`,
            [postId]
        );
    },

    async toggleLike(postId, userId) {
        // 需要创建 likes 表实现完整点赞逻辑
        await db.query(
            `UPDATE posts SET like_count = like_count + 1 
         WHERE post_id = ?`,
            [postId]
        );
    }
};

module.exports = post